Field Object, Fields Collection Example

This example shows what properties are valid for a Field object depending on where the Field resides (for example, the Fields collection of a TableDef, the Fields collection of a QueryDef, and so forth). The FieldOutput procedure is required for this procedure to run.

Sub FieldX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim fldTableDef As Field
    Dim fldQueryDef As Field
    Dim fldRecordset As Field
    Dim fldRelation As Field
    Dim fldIndex As Field
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees")

    ' Assign a Field object from different Fields 
    ' collections to object variables.
    Set fldTableDef = _
        dbsNorthwind.TableDefs(0).Fields(0)
    Set fldQueryDef =dbsNorthwind.QueryDefs(0).Fields(0)
    Set fldRecordset = rstEmployees.Fields(0)
    Set fldRelation =dbsNorthwind.Relations(0).Fields(0)
    Set fldIndex = _
        dbsNorthwind.TableDefs(0).Indexes(0).Fields(0)

    ' Print report.
    FieldOutput "TableDef", fldTableDef
    FieldOutput "QueryDef", fldQueryDef
    FieldOutput "Recordset", fldRecordset
    FieldOutput "Relation", fldRelation
    FieldOutput "Index", fldIndex

    rstEmployees.Close
    dbsNorthwind.Close

End Sub

Sub FieldOutput(strTemp As String, fldTemp As Field)
    ' Report function for FieldX.

    Dim prpLoop As Property

    Debug.Print "Valid Field properties in " & strTemp

    ' Enumerate Properties collection of passed Field
    ' object.
    For Each prpLoop In fldTemp.Properties
        ' Some properties are invalid in certain 
        ' contexts (the Value property in the Fields 
        ' collection of a TableDef for example). Any 
        ' attempt to use an invalid property will 
        ' trigger an error.
        On Error Resume Next
        Debug.Print "  " & prpLoop.Name & " = " & _
            prpLoop.Value
        On Error GoTo 0
    Next prpLoop

End Sub